// backend/dingtalk/utils.js
import axios from "axios";
import db from "../db/mysql.js";
import dayjs from "dayjs";
import {processRecord} from "./updateZhuBoTable.js";
import http from "http";
import https from "https";

/** 小工具：安全 push */
function pushErr(exceptions, msg) {
    if (exceptions && Array.isArray(exceptions)) exceptions.push(msg);
    console.error(msg);
}

/** 获取 access_token */
export async function getDingtalkAccessToken({
                                                 appKey,
                                                 appSecret,
                                                 exceptions, // ✅ 收集器
                                             }) {
    const url = "https://api.dingtalk.com/v1.0/oauth2/accessToken";
    try {
        const {data} = await axios.post(url, {appKey, appSecret});
        const token = data?.accessToken;
        if (!token) {
            pushErr(exceptions, `未获取到 accessToken，返回：${JSON.stringify(data)}`);
            return null;
        }
        console.log(`✅ 成功获取 accessToken（有效期 ${data?.expireIn || 0} 秒）`);
        return token;
    } catch (err) {
        pushErr(exceptions, `获取 access_token 失败：${err.message}`);
        return null;
    }
}

/** 获取表格记录（自动分页） */
export async function getDingtalkSheetRecords({
                                                  baseId,
                                                  sheetName,
                                                  accessToken,
                                                  operatorId,
                                                  exceptions, // ✅ 收集器
                                              }) {
    const urlBase = `https://api.dingtalk.com/v1.0/notable/bases/${baseId}/sheets/${encodeURIComponent(
        sheetName
    )}/records/list${operatorId ? `?operatorId=${operatorId}` : ""}`;

    const allRecords = [];
    let nextToken = "";

    try {
        while (true) {
            const payload = nextToken ? {nextToken} : {};
            const {data} = await axios.post(urlBase, payload, {
                headers: {
                    "x-acs-dingtalk-access-token": accessToken,
                    "Content-Type": "application/json",
                },
                timeout: 30000,
            });

            if (!data?.records) {
                pushErr(
                    exceptions,
                    `获取 ${sheetName} ❌ 请求失败：${JSON.stringify(data)}`
                );
                break;
            }

            allRecords.push(...data.records);
            console.log(`✅ 获取 ${data.records.length} 条数据 (nextToken=${data.nextToken || ""})`);

            if (!data.hasMore || !data.nextToken) break;
            nextToken = data.nextToken;
            await new Promise(resolve => setTimeout(resolve, 10000)); // 5秒延迟
        }
    } catch (err) {
        pushErr(exceptions, `获取 ${sheetName} ❌ 网络或数据异常：${err.message}`);
    }

    console.log(`🎯 共获取 ${allRecords.length} 条记录`);
    return {allRecords, errData: ""};
}

export async function getDingtalkSheetRecordsByFilter({
                                                  baseId,
                                                  sheetName,
                                                  accessToken,
                                                  operatorId,
                                                  exceptions,
                                                  filter, // <-- 这里直接传 filterObject: { items: [...] }
                                              }) {
    const urlBase = `https://api.dingtalk.com/v1.0/notable/bases/${baseId}/sheets/${encodeURIComponent(
        sheetName
    )}/records/list${operatorId ? `?operatorId=${operatorId}` : ""}`;

    const allRecords = [];
    let nextToken = "";

    try {
        while (true) {
            const payload = {};

            if (nextToken) payload.nextToken = nextToken;

            // 🔥 外部直接传入完整 filterObject
            if (filter) {
                payload.filter = filter;
            }

            const { data } = await axios.post(urlBase, payload, {
                headers: {
                    "x-acs-dingtalk-access-token": accessToken,
                    "Content-Type": "application/json",
                },
                timeout: 30000,
            });

            if (!data?.records) {
                pushErr(exceptions, `获取 ${sheetName} ❌：${JSON.stringify(data)}`);
                break;
            }

            allRecords.push(...data.records);

            if (!data.hasMore || !data.nextToken) break;

            nextToken = data.nextToken;
            await new Promise(resolve => setTimeout(resolve, 10000));
        }
    } catch (err) {
        pushErr(exceptions, `获取 ${sheetName} ❌ 网络异常：${err.message}`);
    }

    return { allRecords, errData: "" };
}



/** 只取 recordIds */
export async function getDingtalkSheetRecordIds({
                                                    baseId,
                                                    sheetName,
                                                    accessToken,
                                                    operatorId,
                                                    exceptions, // ✅
                                                }) {
    const {allRecords} = await getDingtalkSheetRecords({
        baseId,
        sheetName,
        accessToken,
        operatorId,
        exceptions,
    });
    return allRecords.map((r) => r.id);
}

/** 插入记录 */
export async function addRecordsToDingtalkSheet_v2({
                                                    accessToken,
                                                    baseId,
                                                    sheetName,
                                                    records,
                                                    operatorId,
                                                    exceptions, // ✅
                                                }) {
    const url = `https://api.dingtalk.com/v1.0/notable/bases/${baseId}/sheets/${encodeURIComponent(
        sheetName
    )}/records${operatorId ? `?operatorId=${operatorId}` : ""}`;

    try {
        const {data} = await axios.post(
            url,
            {records: records.map((r) => ({fields: r}))},
            {
                headers: {
                    "x-acs-dingtalk-access-token": accessToken,
                    "Content-Type": "application/json",
                },
            }
        );
        console.log(`✅ 写入 ${sheetName} 成功`);
        return data;
    } catch (err) {
        const resp = err.response?.data ? JSON.stringify(err.response.data) : err.message;
        pushErr(exceptions, `写入 ${sheetName} 异常：${resp}`);
        return null;
    }
}


/**
 * 将 records 数据分批（每次最多500条）写入钉钉表格
 */
export async function addRecordsToDingtalkSheet({
                                                    accessToken,
                                                    baseId,
                                                    sheetName,
                                                    records,
                                                    operatorId,
                                                    exceptions, // ✅ 错误记录数组
                                                }) {
    const url = `https://api.dingtalk.com/v1.0/notable/bases/${baseId}/sheets/${encodeURIComponent(
        sheetName
    )}/records${operatorId ? `?operatorId=${operatorId}` : ""}`;

    const MAX_BATCH_SIZE = 500;
    let total = records.length;
    let successCount = 0;

    console.log(`📦 开始写入 ${sheetName}，共 ${total} 条记录`);

    // ✅ 分批处理，每次500条
    for (let i = 0; i < total; i += MAX_BATCH_SIZE) {
        const batch = records.slice(i, i + MAX_BATCH_SIZE);

        try {
            const { data } = await axios.post(
                url,
                { records: batch.map((r) => ({ fields: r })) },
                {
                    headers: {
                        "x-acs-dingtalk-access-token": accessToken,
                        "Content-Type": "application/json",
                    },
                }
            );
            successCount += batch.length;
            console.log(`✅ 第 ${Math.floor(i / MAX_BATCH_SIZE) + 1} 批写入成功 (${batch.length} 条)`);
        } catch (err) {
            const resp = err.response?.data ? JSON.stringify(err.response.data) : err.message;
            pushErr(exceptions, `写入 ${sheetName} 第 ${Math.floor(i / MAX_BATCH_SIZE) + 1} 批异常：${resp}`);
            console.error(`❌ 第 ${Math.floor(i / MAX_BATCH_SIZE) + 1} 批写入失败：`, resp);
        }
    }

    console.log(`🏁 ${sheetName} 写入完成，共写入 ${successCount}/${total} 条记录`);
}


/** 批量删除记录 */
export async function deleteRecordsFromSheet_v1({
                                                 baseId,
                                                 sheetName,
                                                 recordIds,
                                                 accessToken,
                                                 operatorId,
                                                 exceptions, // ✅
                                             }) {
    const url = `https://api.dingtalk.com/v1.0/notable/bases/${baseId}/sheets/${encodeURIComponent(
        sheetName
    )}/records/delete${operatorId ? `?operatorId=${operatorId}` : ""}`;

    const batchSize = 100;
    if (!recordIds?.length) {
        console.log("⚠️ 没有可删除的记录。");
        return "1";
    }

    let ok = "1";
    console.log(`共 ${recordIds.length} 条记录，将分批删除（每批 ${batchSize}）...`);
    for (let i = 0; i < recordIds.length; i += batchSize) {
        const batch = recordIds.slice(i, i + batchSize);
        try {
            await axios.post(
                url,
                {recordIds: batch},
                {
                    headers: {
                        "x-acs-dingtalk-access-token": accessToken,
                        "Content-Type": "application/json",
                    },
                }
            );
            console.log(`✅ 第 ${Math.floor(i / batchSize) + 1} 批删除成功`);
        } catch (err) {
            ok = "0";
            pushErr(
                exceptions,
                `⚠️ 第 ${Math.floor(i / batchSize) + 1} 批删除 ${sheetName} 异常：${err.message}`
            );
        }
        if (i + batchSize < recordIds.length) {
            console.log("⏳ 等待3秒后继续删除…");
            await new Promise((r) => setTimeout(r, 3000));
        }
    }
    console.log("✅ 所有批次删除完成。");
    return ok;
}


/**
 * 从钉钉表格中批量删除记录（带自动重试 + 限速保护 + 进度显示）
 */
export async function deleteRecordsFromSheet({
                                                 baseId,
                                                 sheetName,
                                                 recordIds,
                                                 accessToken,
                                                 operatorId,
                                                 exceptions,
                                             }) {
    const url = `https://api.dingtalk.com/v1.0/notable/bases/${baseId}/sheets/${encodeURIComponent(
        sheetName
    )}/records/delete${operatorId ? `?operatorId=${operatorId}` : ""}`;

    const batchSize = 100;
    let ok = "1";

    if (!recordIds?.length) {
        console.log("⚠️ 没有可删除的记录。");
        return ok;
    }

    console.log(`🧾 共 ${recordIds.length} 条记录，将分批删除（每批 ${batchSize} 条）...`);

    // ✅ axios 实例（ESM 用法）
    const client = axios.create({
        timeout: 15000, // 15秒超时
        httpAgent: new http.Agent({ keepAlive: false }),
        httpsAgent: new https.Agent({ keepAlive: false }),
        headers: {
            "x-acs-dingtalk-access-token": accessToken,
            "Content-Type": "application/json",
        },
    });

    for (let i = 0; i < recordIds.length; i += batchSize) {
        const batch = recordIds.slice(i, i + batchSize);
        const batchIndex = Math.floor(i / batchSize) + 1;
        let retry = 0;
        let success = false;

        while (!success && retry < 3) {
            try {
                await client.post(url, { recordIds: batch });

                console.log(`✅ 第 ${batchIndex} 批删除成功（${batch.length} 条）`);
                success = true;
            } catch (err) {
                retry++;
                ok = "0";
                const msg = `⚠️ 第 ${batchIndex} 批删除 ${sheetName} 异常（第 ${retry} 次尝试）: ${
                    err.code || err.message
                }`;

                console.warn(msg);
                if (exceptions) exceptions.push(msg);

                if (err.code === "ECONNRESET" || err.message.includes("socket hang up")) {
                    console.log("🔁 检测到连接断开，等待 2 秒后重试...");
                    await wait(2000);
                    continue;
                }

                if (retry < 3) {
                    console.log("⏳ 等待 3 秒后重试...");
                    await wait(3000);
                } else {
                    console.error(`❌ 第 ${batchIndex} 批删除彻底失败，跳过该批次。`);
                }
            }
        }

        const progress = Math.min(((i + batchSize) / recordIds.length) * 100, 100).toFixed(1);
        console.log(`📊 删除进度：${progress}%`);

        if (i + batchSize < recordIds.length) {
            console.log("⏳ 等待 3 秒后继续删除下一批...");
            await wait(3000);

        }
    }

    console.log(ok === "1" ? "✅ 所有批次删除完成。" : "⚠️ 删除任务中有异常，请检查 exceptions。");
    return ok;
}



/** 延时函数 */
function wait(ms) {
    return new Promise((r) => setTimeout(r, ms));
}

/** 更新 dd_self_live_summary（用你项目里的 db/mysql.js） */
export async function updateDdSelfLiveSummary(insertDatas = [], exceptions) {
    try {
        await db.transaction(async (conn) => {
            // 删除旧数据
            await conn.execute("DELETE FROM dd_self_live_summary");

            // 批量插入新数据
            const columns = [
                "month", "date", "douyin_nickname", "anchor", "daily_gmv",
                "daily_cost", "order_subsidy", "hanging_fee", "roi", "actual_roi",
                "daily_live_mins", "daily_live_hours", "monthly_gmv", "monthly_cost",
                "monthly_actual_roi", "monthly_live_hours"
            ];

            // 分批执行插入（事务内）
            const batchSize = 5000;
            for (let i = 0; i < insertDatas.length; i += batchSize) {
                const batch = insertDatas.slice(i, i + batchSize);
                const placeholders = batch.map(row => `(${row.map(() => '?').join(',')})`).join(',');
                const sql = `INSERT INTO dd_self_live_summary (${columns.map(c => `\`${c}\``).join(',')}) VALUES ${placeholders}`;
                const values = batch.flat();
                await conn.execute(sql, values);
            }

            //更新店铺
            const  update_shopname_sql=`UPDATE dd_self_live_summary AS a,dd_douyin_shop_commission_rate AS b SET a.shop_name=b.shop_name WHERE a.douyin_nickname=b.douyin_nickname;`
            await conn.execute(update_shopname_sql);

        });

        console.log("✅ dd_self_live_summary 更新成功（使用事务提交）");
    } catch (err) {
        console.error("❌ dd_self_live_summary 更新失败：", err.message);
        if (exceptions) exceptions.push(`数据库更新失败（事务回滚）：${err.message}`);
    }
}


/*获取分佣表*/
export async function getCommissionDatas({
                                             baseId,
                                             sheetName,
                                             accessToken,
                                             operatorId,
                                             exceptions, // ✅ 收集器
                                         }) {
    const fen_yong_json = {}
    try {
        const {allRecords} = await getDingtalkSheetRecords({
            baseId,
            sheetName,
            accessToken,
            operatorId,
            exceptions,
        });


        for (const record of allRecords) {
            const fields = record.fields;
            const fen_yong = fields["分佣"]
            const shop_name = fields["店铺名称"]
            const nick_name = fields["抖音昵称"]
            if (!(shop_name in fen_yong_json)) {
                fen_yong_json[shop_name] = {}
            }
            fen_yong_json[shop_name][nick_name] = fen_yong
        }
    } catch (e) {
        console.log(`分佣表获取异常：${e.message}`)
        pushErr(exceptions, `分佣表获取异常：${e.message}`)
    }
    console.log(fen_yong_json)
    return fen_yong_json
}


/**
 * 合并店铺汇总数据与每日明细数据
 * @param {Array} summaryArr 店铺汇总数组
 * @param {Array} dailyArr 每日明细数组
 * @returns {Array} 合并后的新数组，每个店铺包含 line 结构
 */
export async function mergeShopDataWithFill(summaryArr, dailyArr, allDates) {
    return summaryArr.map((shop) => {
        const name = shop.shop_name;

        // 取出该店铺每日数据
        const dailyList = dailyArr.filter((d) => d.shop_name === name);

        // 初始化 line 结构
        const line = {
            yong_hu_zhi_fu_price: {},
            tui_kuan_price: {},
            qian_chuan_cost: {},
        };

        // 遍历整个日期范围，逐天补齐
        allDates.forEach((date) => {
            // 查找当天的数据（可能为空）
            const dayData = dailyList.find((d) => d.date_str === date);

            // 如果当天有数据就用原值，否则默认 0
            line.yong_hu_zhi_fu_price[date] = {
                shop_name: name,
                value: dayData ? dayData.yong_hu_zhi_fu_price : "0.00",
            };
            line.tui_kuan_price[date] = {
                shop_name: name,
                value: dayData ? dayData.tui_kuan_price : "0.00",
            };
            line.qian_chuan_cost[date] = {
                shop_name: name,
                value: dayData ? dayData.qian_chuan_cost : "0.00",
            };
        });

        return { ...shop, line };
    });
}


/**
 * 根据起始日期和结束日期计算完整日期范围
 * 支持 "YYYY-MM" 或 "YYYY-MM-DD" 格式
 * 例如：
 *  getDateRange("2025-09", "2025-10")
 *  => ["2025-09-01", ..., "2025-10-31"]
 *
 * @param {string} start 起始日期（"2025-09" 或 "2025-09-10"）
 * @param {string} end 结束日期（"2025-10" 或 "2025-10-31"）
 * @returns {Array<string>} 日期数组
 */
export async function getDateRange(start, end) {
    // 如果只传到月份，例如 "2025-09"
    const normalizeDate = (str, type) => {
        if (/^\d{4}-\d{2}$/.test(str)) {
            // 如果是 YYYY-MM 格式
            if (type === "start") return `${str}-01`;
            if (type === "end") {
                const [y, m] = str.split("-").map(Number);
                const lastDay = new Date(y, m, 0).getDate(); // 获取当月最后一天
                return `${str}-${String(lastDay).padStart(2, "0")}`;
            }
        }
        return str; // 如果原本就有日
    };

    const startDate = normalizeDate(start, "start");
    const endDate = normalizeDate(end, "end");

    const result = [];
    let current = new Date(startDate);
    const endObj = new Date(endDate);

    while (current <= endObj) {
        const y = current.getFullYear();
        const m = String(current.getMonth() + 1).padStart(2, "0");
        const d = String(current.getDate()).padStart(2, "0");
        result.push(`${y}-${m}-${d}`);
        current.setDate(current.getDate() + 1);
    }

    return result;
}


/**
 * 格式化数字，大于1万用“万”为单位显示
 * @param {number|string} num - 输入数字
 * @returns {string}
 */
export async function formatNumber(num) {
    const n = Number(num)
    if (isNaN(n)) return '0'
    if (n >= 10000) {
        return (n / 10000).toFixed(2).replace(/\.00$/, '') + '万'
    }
    return n.toString()
}

/**
 * 获取上个周期的时间范围
 * @param {string} start - 当前周期开始时间，如 "2025-10-01" 或 "2025-10"
 * @param {string} end - 当前周期结束时间，如 "2025-10-10" 或 "2025-11"
 * @returns {{ start: string, end: string }} 上个周期的时间范围
 */
export async function getPreviousPeriod(start, end) {
    // 判断是按月还是按日
    const isMonthMode = start.length === 7 && end.length === 7; // 'YYYY-MM'

    if (isMonthMode) {
        // ✅ 月份模式
        const startMonth = dayjs(start + "-01");
        const endMonth = dayjs(end + "-01");

        // 计算当前周期的月数跨度
        const diffMonths = endMonth.diff(startMonth, "month") + 1;

        // 上个周期的结束月份
        const prevEnd = startMonth.subtract(1, "month");
        const prevStart = prevEnd.subtract(diffMonths - 1, "month");

        return {
            start: prevStart.format("YYYY-MM"),
            end: prevEnd.format("YYYY-MM"),
        };
    } else {
        // ✅ 日期模式
        const startDate = dayjs(start);
        const endDate = dayjs(end);
        const diffDays = endDate.diff(startDate, "day") + 1; // 当前周期天数

        const prevEnd = startDate.subtract(1, "day");
        const prevStart = prevEnd.subtract(diffDays - 1, "day");

        return {
            start_pre: prevStart.format("YYYY-MM-DD"),
            end_pre: prevEnd.format("YYYY-MM-DD"),
        };
    }
}

/*保留2位小数*/
export async function roundTo2(num) {
    return Math.round(num * 100) / 100;
}


